// ~ Package Declaration
// ==================================================

package com.wms.data.dao.jdbc;

import com.wms.data.DataException;
import com.wms.data.PaginateModel;
import com.wms.data.WmsFrameworkException;

import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


// ~ Comments
// ==================================================

/**
 * 
 * database type.
 * 
 * <pre>
 * 歷史�?���?
 * 2010-2-6 Timothy
 * 	新建檔案
 * </pre>
 * 
 * @author <pre>
 * SD
 * 	Timothy
 * PG
 * 
 * UT
 * 
 * MA
 * </pre>
 * @version $Rev$
 * 
 *          <p/>
 *          $Id$
 * 
 */
public enum DatabaseType {

	// ~ Enums
	// ==================================================

	ORACLE {

		/*
		 * 2010-2-10 Timothy
		 * 
		 * @see com.vs.data.dao.jdbc.DatabaseType#generatePaginateSql(java.lang.CharSequence, com.vs.data.PaginateModel)
		 */
		@Override
		public String generateLimitSql(CharSequence sql, boolean hasOffset) {

			String limitSql = hasOffset ? " SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( %s ) ROW_ WHERE ROWNUM <= ?) WHERE ROWNUM_ > ? "
					: " SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( %s ) ROW_ WHERE ROWNUM <= ? ";

			return this.showSql(String.format(limitSql, sql), "Limit sql");
		}

		/*
		 * 2010-2-10 Timothy
		 * 
		 * @see com.vs.data.dao.jdbc.DatabaseType#generatePaginateSql(java.lang.CharSequence, com.vs.data.PaginateModel)
		 */
		@Override
		public String generatePaginateSql(CharSequence sql, PaginateModel paginateModel) {

			String paginateSql = " SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( %s ) ROW_ WHERE ROWNUM <= %s) WHERE ROWNUM_ > %s ";
			int pageSize = paginateModel.getPageSize();
			int pageNo = paginateModel.getPageNo();

			return this.showSql(String.format(paginateSql, sql, pageSize * pageNo, pageSize * (pageNo - 1)), "Paginate sql");
		}

		/*
		 * 2010-3-10 Timothy
		 * 
		 * @see com.vs.data.dao.jdbc.DatabaseType#getSeqNextValue(java.lang.String)
		 */
		@Override
		public String getSequenceNextValue(String primaryKeySequence) {

			return String.format(" SELECT %s.NEXTVAL FROM DUAL ", primaryKeySequence);
		}
	},

	DB2,

	SQLSERVER,

	INFORMIX,

	MYSQL,

	HSQL {

		/*
		 * 2010-2-10 Timothy
		 * 
		 * @see com.vs.data.dao.jdbc.DatabaseType#generatePaginateSql(java.lang.CharSequence, com.vs.data.PaginateModel)
		 */
		@Override
		public String generateLimitSql(CharSequence sql, boolean hasOffset) {

			return this.showSql(new StringBuffer(sql.length() + 10).append(sql).insert(sql
				.toString()
					.toLowerCase()
					.indexOf("select") + 6,
				hasOffset ? " limit ? ? " : " top ? ").toString(), "Limit sql");
		}

		/*
		 * 2010-2-10 Timothy
		 * 
		 * @see com.vs.data.dao.jdbc.DatabaseType#generatePaginateSql(java.lang.CharSequence, com.vs.data.PaginateModel)
		 */
		@Override
		public String generatePaginateSql(CharSequence sql, PaginateModel paginateModel) {

			String paginateSql = new StringBuffer(sql.length() + 10).append(sql).insert(sql
				.toString()
					.toLowerCase()
					.indexOf("select") + 6,
				" limit %s %s ").toString();
			int pageSize = paginateModel.getPageSize();
			int pageNo = paginateModel.getPageNo();

			return this.showSql(String.format(paginateSql, pageSize * (pageNo - 1), pageSize * pageNo), "Paginate sql");
		}
	},

	DERBY;

	// ~ Static Fields
	// ==================================================

	// ~ Fields
	// ==================================================

	Log logger = LogFactory.getLog(this.getClass());

	// ~ Constructors
	// ==================================================

	// ~ Methods
	// ==================================================

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * 产生限定分页语句
	 * </pre>
	 * 
	 * @param sql
	 * @param paginateModel
	 * @return
	 */
	public String generatePaginateSql(CharSequence sql, PaginateModel paginateModel) {
		
//		String paginateSql = new StringBuffer(sql.length() + 10).append(sql).insert(
//				sql.toString().toLowerCase().indexOf("select") + 6, " limit %s %s ").toString();
		
		String paginateSql = new StringBuffer(sql).append(" limit %s, %s ").toString();
		
		int pageSize = paginateModel.getPageSize();
		int pageNo = paginateModel.getPageNo();

		return this.showSql(String.format(paginateSql, pageSize * (pageNo - 1), pageSize * pageNo), "Paginate sql");
	}

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * 产生限定�?sql，即限定查询前几笔数�?
	 * </pre>
	 * 
	 * @param sql
	 * @return
	 */
	public String generateLimitSql(CharSequence sql) {

		return this.generateLimitSql(sql, false);
	}

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * 产生限定�?sql (有参�?Hibernate source 实现)
	 * </pre>
	 * 
	 * @param sql
	 * @param hasOffset
	 *          是否有偏�?
	 * @return
	 */
	public String generateLimitSql(CharSequence sql, boolean hasOffset) {

		throw new WmsFrameworkException(new UnsupportedOperationException("此方法尚未完成实�?.. "));
	}

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * 获取 Count sql ==> [ SELECT COUNT(*) COUNT FROM (:sql) ORACLE_COUNT ].
	 * </pre>
	 * 
	 * @param sql
	 * @return
	 */
	public String generateCountSql(CharSequence sql) {

		return this.generateCountSql(sql, null, null);
	}

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * 获取 Count sql ==> [ SELECT COUNT(*) :countAlias FROM (:sql) ORACLE_COUNT ].
	 * </pre>
	 * 
	 * @param sql
	 * @param countAlias
	 *          count 栏位别名
	 * @return
	 */
	public String generateCountSql(CharSequence sql, String countAlias) {

		return this.generateCountSql(sql, countAlias, null);
	}

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * 获取 Count sql ==> [ SELECT COUNT(*) :countAlias FROM (:sql) :subSqlAlias ].
	 * </pre>
	 * 
	 * @param sql
	 * @param countAlias
	 *          count 栏位别名
	 * @param subSqlAlias
	 *          sql 语句做为子查询时的别�?
	 * @return
	 */
	public String generateCountSql(CharSequence sql, String countAlias, String subSqlAlias) {

		return this.showSql(String.format(" SELECT COUNT(*) %s FROM (%s) %s ", StringUtils.defaultIfEmpty(countAlias,
			"COUNT_"), sql, StringUtils.defaultIfEmpty(subSqlAlias, String.format("%s_COUNT", this.name()))), "Count sql");
	}

	/**
	 * <pre>
	 * 2010-2-23 Timothy
	 * 	�?{@link Clob} convert to {@link String}
	 * </pre>
	 * 
	 * @param clob
	 * @return
	 */
	public String convertClobToString(Clob clob) {

		if (clob == null) {
			return "";
		}

		try {

			char[] content = new char[(int) clob.length()];
			Reader reader = clob.getCharacterStream();

			reader.read(content);
			reader.close();

			return new String(content);
		}
		catch (Exception e) {

			throw new DataException(e);
		}
	}

	/**
	 * <pre>
	 * 2010-3-17 Timothy
	 * </pre>
	 * 
	 * @param blob
	 * @return
	 */
	public byte[] convertBlobToBytes(Blob blob) {

		if (blob == null) {
			return new byte[0];
		}

		try {
			return blob.getBytes(1, (int) blob.length());
		}
		catch (Exception e) {

			throw new DataException(e);
		}
	}

	/**
	 * <pre>
	 * 2010-2-10 Timothy
	 * </pre>
	 * 
	 * @param sql
	 * @param title
	 */
	String showSql(CharSequence sql, String title) {

		if (this.logger.isDebugEnabled()) {
			this.logger.debug(String.format("%s : [%s]", title, sql));
		}
		
		return sql.toString();
	}

	/**
	 * <pre>
	 * 2010-3-10 Timothy
	 * 获取查询序列的下�?��值的sql语句
	 * </pre>
	 * 
	 * @param primaryKeySequence
	 * @return
	 */
	public String getSequenceNextValue(String primaryKeySequence) {

		throw new DataException(new UnsupportedOperationException("尚不支援此方�?.. "));
	}

}
